#!/use/bin/python
# coding=utf-8
import datetime

import MySQLdb


def getConnection():
    conn = MySQLdb.connect(
        host='172.20.154.103',
        port=3307,
        user='control',
        passwd='ugTd6ZJ7oHIzif',
        db='device_remote_control',
        charset='utf8',
    )
    cur = conn.cursor()
    return conn, cur


# 记录 WEB发起连接的总数(表rep_link_info_log)
# data 为 'yyyy-MM-dd' 的字符串 例:‘2021-12-14’
def insertRepStartNum(date):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        selectCountSql = """
            select count(1) from  rep_link_rate where DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
        """ % date;
        cur.execute(selectCountSql)
        # 查询单条数据
        result = cur.fetchone()
        countNum = result[0]
        # 获取WEB发起连接的总数
        selectNumSql = """
            select count(1) start_num   from rep_link_info_log t where t.`status` =1 and DATE_FORMAT(trigger_time, '%%Y-%%m-%%d') = '%s'
        """ % date
        cur.execute(selectNumSql)
        # 查询单条数据
        resultNum = cur.fetchone()
        if countNum == 0:
            print ("新增数据,WEB发起连接的总数,数量为%s" % resultNum[0])
            # 插入报表，记录WEB发起连接的总数
            insertSql = """insert into rep_link_rate(success_rate,error_rate,analysis_date,start_num) 
                value(100,0,'%s',%s)""" % (date, resultNum[0])
            cur.execute(insertSql)
        else:
            print ("更新数据,WEB发起连接的总数,数量为%s" % resultNum[0])
            # 更新报表，记录WEB发起连接的总数
            updateSql = """
                update rep_link_rate set start_num = %s ,update_time = now()  where
                    DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
            """ % (resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 客户端拒绝授权的总数(表rep_push_response_log)
# data 为 'yyyy-MM-dd' 的字符串 例:‘2021-12-14’
def insertRepRefuseNum(date):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        selectCountSql = """
            select count(1) from  rep_link_rate where DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
        """ % date;
        cur.execute(selectCountSql)
        # 查询单条数据
        result = cur.fetchone()
        countNum = result[0]
        # 获取WEB发起连接的总数
        selectNumSql = """
            select count(1) refuse_num from rep_push_response_log t where t.`refuse_flag` =1
             and DATE_FORMAT(refuse_time, '%%Y-%%m-%%d') = '%s'
        """ % date
        cur.execute(selectNumSql)
        # 查询单条数据
        resultNum = cur.fetchone()
        if countNum == 0:
            print ("新增数据,客户端拒绝授权的总数,数量为%s" % resultNum[0])
            # 插入报表，记录客户端拒绝授权的总数(
            insertSql = """insert into rep_link_rate(success_rate,error_rate,analysis_date,refuse_num) 
                value(100,0,'%s',%s)""" % (date, resultNum[0])
            cur.execute(insertSql)
        else:
            print ("更新数据,客户端拒绝授权的总数,数量为%s" % resultNum[0])
            # 更新报表，记录客户端拒绝授权的总数(
            updateSql = """
                update rep_link_rate set refuse_num = %s ,update_time = now()  where
                    DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
            """ % (resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# web端成功连接websocket的总数(表rep_link_info_log)
# data 为 'yyyy-MM-dd' 的字符串 例:‘2021-12-14’
def insertRepSuccessNum(date):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        selectCountSql = """
            select count(1) from  rep_link_rate where DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
        """ % date;
        cur.execute(selectCountSql)
        # 查询单条数据
        result = cur.fetchone()
        countNum = result[0]
        # 获取WEB发起连接的总数
        selectNumSql = """
            select count(1) success_num   from rep_link_info_log t where t.`status` =2 and t.source = 'WEB' and DATE_FORMAT(trigger_time, '%%Y-%%m-%%d') = '%s'
        """ % date
        cur.execute(selectNumSql)
        # 查询单条数据
        resultNum = cur.fetchone()
        if countNum == 0:
            print ("新增数据,web端成功连接websocket的总数,数量为%s" % resultNum[0])
            # 插入报表，记录web端成功连接websocket的总数
            insertSql = """insert into rep_link_rate(success_rate,error_rate,analysis_date,success_num)
                value(100,0,'%s',%s)""" % (date, resultNum[0])
            cur.execute(insertSql)
        else:
            print ("更新数据,web端成功连接websocket的总数,数量为%s" % resultNum[0])
            # 更新报表，记录web端成功连接websocket的总数
            updateSql = """
                update rep_link_rate set success_num = %s ,update_time = now()  where
                    DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
            """ % (resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 异常次数(表rep_link_error_log)
# data 为 'yyyy-MM-dd' 的字符串 例:‘2021-12-14’
def insertRepErrorNum(date):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        selectCountSql = """
            select count(1) from  rep_link_rate where DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
        """ % date;
        cur.execute(selectCountSql)
        # 查询单条数据
        result = cur.fetchone()
        countNum = result[0]
        # 获取异常次数
        selectNumSql = """
            select count(1) error_num   from rep_link_error_log t where  DATE_FORMAT(create_time, '%%Y-%%m-%%d') = '%s'
        """ % date
        cur.execute(selectNumSql)
        # 查询单条数据
        resultNum = cur.fetchone()
        if countNum == 0:
            print ("新增数据,异常次数,数量为%s" % resultNum[0])
            # 插入报表，记录异常次数
            insertSql = """insert into rep_link_rate(success_rate,error_rate,analysis_date,error_num) 
                value(100,0,'%s',%s)""" % (date, resultNum[0])
            cur.execute(insertSql)
        else:
            print ("更新数据,异常次数,数量为%s" % resultNum[0])
            # 更新报表，记录异常次数
            updateSql = """
                update rep_link_rate set error_num = %s ,update_time = now()  where
                    DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
            """ % (resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 异常断开次数(表rep_link_info_log)
# data 为 'yyyy-MM-dd' 的字符串 例:‘2021-12-14’
def insertRepErrorOffNum(date):
    try:
        conn, cur = getConnection()
        # 使用cursor()方法获取操作游标
        cur = conn.cursor()
        selectCountSql = """
            select count(1) from  rep_link_rate where DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
        """ % date;
        cur.execute(selectCountSql)
        # 查询单条数据
        result = cur.fetchone()
        countNum = result[0]
        # 获取WEB发起连接的总数
        selectNumSql = """
            select count(1) error_off_num   from rep_link_info_log t where t.`status` =3 and t.close_code not in(1000,1002) 
                and DATE_FORMAT(trigger_time, '%%Y-%%m-%%d') = '%s'
        """ % date
        cur.execute(selectNumSql)
        # 查询单条数据
        resultNum = cur.fetchone()
        if countNum == 0:
            print ("新增数据,异常断开次数,数量为%s" % resultNum[0])
            # 插入报表，记录异常断开次数
            insertSql = """insert into rep_link_rate(success_rate,error_rate,analysis_date,error_off_num) 
            value(100,0,'%s',%s)""" % (date, resultNum[0])
            cur.execute(insertSql)
        else:
            print ("更新数据,异常断开次数,数量为%s" % resultNum[0])
            # 更新报表，记录异常断开次数
            updateSql = """
                update rep_link_rate set error_off_num = %s ,update_time = now()  where
                    DATE_FORMAT(analysis_date, '%%Y-%%m-%%d') = '%s'
            """ % (resultNum[0], date);
            cur.execute(updateSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date


if __name__ == '__main__':
    #today = todayYMD()
    today = getdate(1)
    #today = "2021-12-13"
    print ("%s 日期,更新报表" % today)
    insertRepStartNum(today)
    insertRepRefuseNum(today)
    insertRepSuccessNum(today)
    insertRepErrorNum(today)
    insertRepErrorOffNum(today)
